package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.complaintModel;
import model.userModel;
import util.dataUtil;

public class complaintdao {
	public static int updateComplaint(complaintModel model) {
		int result = 0;
		try {

			
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_UpdateComplaint(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			
		
			csmt.setInt("ID", model.getId());
			csmt.setInt("ID_COM_TYPE", model.getID_com_type());
			csmt.setString("name", model.getName());
			csmt.setInt("age", model.getAge());
			csmt.setInt("sex", model.getSex());
			csmt.setString("address", model.getAddress());
			csmt.setString("phone", model.getPhone());
			csmt.setString("email", model.getEmail());
			csmt.setString("detail", model.getDetail());
			csmt.setString("date", model.getDate());
			csmt.setString("place", model.getAddress());
			csmt.setInt("idUser", model.getIduser());
			csmt.setString("Last_updated", model.getLast_updated());
			csmt.setString("complaint_name", model.getComplaint_name());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);

			csmt.execute();

			
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
			e.printStackTrace();
		result = 0;
		}
		return result;
		}
	//delete complaint
	public static int deleteComplaint(complaintModel model) {
		int result = 0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call SP_DeleteComplaint_reg(?,?)}");
			csmt.setInt("ID", model.getId());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);
			csmt.execute();
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
		result = 0;
		}
		return result;
			
		}
	//add user
	public static int InsertComplaint(complaintModel model) {
		int result = 0;
		try {

			
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_InsertComplaint(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			csmt.setInt("ID_COM_TYPE", model.getID_com_type());
			csmt.setString("name", model.getName());
			csmt.setInt("age", model.getAge());
			csmt.setInt("sex", model.getSex());
			csmt.setString("address", model.getAddress());
			csmt.setString("phone", model.getPhone());
			csmt.setString("email", model.getEmail());
			csmt.setString("detail", model.getDetail());
			csmt.setString("date", model.getDate());
			csmt.setString("place", model.getAddress());
			csmt.setInt("idUser", model.getIduser());
			csmt.setString("Last_updated", model.getLast_updated());
			csmt.setString("complaint_name", model.getComplaint_name());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);

			csmt.execute();

			
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
			e.printStackTrace();
		result = 0;
		}
		return result;
		}
	public static ArrayList getAllComplaint() {
		ArrayList listComplaint = new ArrayList();
		String sql = "SELECT * FROM Complaint_reg ORDER BY ID";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				complaintModel model = new complaintModel();
				model.setId(rs.getInt("ID"));
				model.setID_com_type(rs.getInt("ID_COM_TYPE"));
				model.setName(rs.getString("Name"));
				model.setAge(rs.getInt("age"));
				model.setSex(rs.getInt("sex"));
				model.setAddress(rs.getString("address"));
				model.setPhone(rs.getString("Phone"));
				model.setEmail(rs.getString("Email"));
				model.setDetail(rs.getString("Detail"));
				model.setDate(rs.getString("date"));
				model.setPlace(rs.getString("Place"));
				model.setIduser(rs.getInt("IDUser"));
				model.setLast_updated(rs.getString("last_Updated"));
				listComplaint.add(model);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return listComplaint;
	}
	//search complaint
	public static ArrayList getSearchComplaint(String name) {
		ArrayList listComplaint = new ArrayList();
		String sql = "SELECT * FROM Complaint_reg where name like '%"+name+"%'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				complaintModel model = new complaintModel();
				model.setId(rs.getInt("ID"));
				model.setID_com_type(rs.getInt("ID_COM_TYPE"));
				model.setName(rs.getString("Name"));
				model.setAge(rs.getInt("age"));
				model.setSex(rs.getInt("sex"));
				model.setAddress(rs.getString("address"));
				model.setPhone(rs.getString("Phone"));
				model.setEmail(rs.getString("Email"));
				model.setDetail(rs.getString("Detail"));
				model.setDate(rs.getString("date"));
				model.setPlace(rs.getString("Place"));
				model.setIduser(rs.getInt("IDUser"));
				model.setLast_updated(rs.getString("last_Updated"));
				listComplaint.add(model);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return listComplaint;
	}
	
	public static ArrayList<complaintModel> getdataComplaintByID(int id) {
		String sql = "SELECT * FROM Complaint_reg WHERE id=?";
				
				ArrayList<complaintModel> modelid = new ArrayList<complaintModel>();
				try {
					PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
					ps.setInt(1, id);
					ResultSet rs = ps.executeQuery();
					while (rs.next()) {
						complaintModel model = new complaintModel();
						model.setId(rs.getInt("ID"));
						model.setID_com_type(rs.getInt("ID_COM_TYPE"));
						model.setName(rs.getString("Name"));
						model.setAge(rs.getInt("age"));
						model.setSex(rs.getInt("sex"));
						model.setAddress(rs.getString("address"));
						model.setPhone(rs.getString("Phone"));
						model.setEmail(rs.getString("Email"));
						model.setDetail(rs.getString("Detail"));
						model.setDate(rs.getString("date"));
						model.setPlace(rs.getString("Place"));
						model.setIduser(rs.getInt("IDUser"));
						model.setLast_updated(rs.getString("last_Updated"));
						modelid.add(model);
					}
				}
				catch (Exception e) 
				{
					e.printStackTrace();
				}
				
				return modelid;
			}
}
